﻿using FinancialReportPowerPointAddIn.Helper;
using Microsoft.EntityFrameworkCore;
using Microsoft.Office.Interop.PowerPoint;
using System;
using System.Data;
using System.Linq;
using PpChartType = Microsoft.Office.Core.XlChartType;

namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GenerateMTDSalesDifferences
    {
        public static void Write(int year, int month, int comYear, int comMonth)
        {
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            using (var db = new FinancialReportContext())
            {
                //先准备数据
                //平台数据
                var currentPlatformSalesData = db.PlatformSales.Where(x => x.Year == year && x.Month == month).AsNoTracking();
                var comPlatformSalesData = db.PlatformSales.Where(x => x.Year == comYear && x.Month == comMonth).AsNoTracking();
                decimal ctotal, fc, mds, kiosk, remotekiosk, comtotal;
                ctotal = currentPlatformSalesData.Sum(x => x.全店营业净收入);
                fc = currentPlatformSalesData.Sum(x => x.FC营业净收入) - comPlatformSalesData.Sum(x => x.FC营业净收入);
                mds = currentPlatformSalesData.Sum(x => x.MDS营业净收入) - comPlatformSalesData.Sum(x => x.MDS营业净收入);
                kiosk = currentPlatformSalesData.Sum(x => x.Kiosk营业净收入) - comPlatformSalesData.Sum(x => x.Kiosk营业净收入);
                remotekiosk = currentPlatformSalesData.Sum(x => x.RemoteKiosk营业净收入) - comPlatformSalesData.Sum(x => x.RemoteKiosk营业净收入);
                comtotal = comPlatformSalesData.Sum(x => x.全店营业净收入);
                //daypart数据
                var cstart = new DateTime(year, month, 1);
                var cend = new DateTime(year, month, DateTime.DaysInMonth(year, month));
                var comstart = new DateTime(comYear, comMonth, 1);
                var comend = new DateTime(comYear, comMonth, DateTime.DaysInMonth(comYear, comMonth));
                var currentDaypartData = db.PeakHourSales.Where(x => x.PeakDate >= cstart && x.PeakDate <= cend).AsNoTracking();
                var comDaypartData = db.PeakHourSales.Where(x => x.PeakDate >= comstart && x.PeakDate <= comend).AsNoTracking();
                decimal comdaytotal, cdaytotal, breakfast, lunch, teatime, dinner, nightsnack, midnight;
                comdaytotal = comDaypartData.Sum(x => x.全店Sales);
                cdaytotal = currentDaypartData.Sum(x => x.全店Sales);
                breakfast = currentDaypartData.Where(x => x.Category == "早餐").Sum(x => x.全店Sales) - comDaypartData.Where(x => x.Category == "早餐").Sum(x => x.全店Sales);
                lunch = currentDaypartData.Where(x => x.Category == "午餐").Sum(x => x.全店Sales) - comDaypartData.Where(x => x.Category == "午餐").Sum(x => x.全店Sales);
                teatime = currentDaypartData.Where(x => x.Category == "下午茶").Sum(x => x.全店Sales) - comDaypartData.Where(x => x.Category == "下午茶").Sum(x => x.全店Sales);
                dinner = currentDaypartData.Where(x => x.Category == "晚餐").Sum(x => x.全店Sales) - comDaypartData.Where(x => x.Category == "晚餐").Sum(x => x.全店Sales);
                nightsnack = currentDaypartData.Where(x => x.Category == "宵夜").Sum(x => x.全店Sales) - comDaypartData.Where(x => x.Category == "宵夜").Sum(x => x.全店Sales);
                midnight = currentDaypartData.Where(x => x.Category == "通宵").Sum(x => x.全店Sales) - comDaypartData.Where(x => x.Category == "通宵").Sum(x => x.全店Sales);
                //开始写入数据
                //写入标题           
                Shape titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 50, 50, 420, 30);
                titleBox.TextFrame.TextRange.Text = $"营业额平台和时段差异-{year}{month.ToString("00")}VS{comYear}{comMonth.ToString("00")}";
                titleBox.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoCTrue;
                titleBox.TextFrame.TextRange.Font.Size = 20;
                //写入总体同比
                titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 95, 81, 270, 30);
                string salesDiff = string.Empty;
                salesDiff = OutputDiffString(ctotal - comtotal);
                titleBox.TextFrame.TextRange.Text = $"Sales{salesDiff}万同比VS.{comYear}";
                titleBox.TextFrame.TextRange.Font.Size = 18;
                //写入时段差异
                titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 500, 50, 420, 30);
                titleBox.TextFrame.TextRange.Text = $"早餐{OutputDiffString(breakfast)}万，午餐{OutputDiffString(lunch)}万，下午茶{OutputDiffString(teatime)}万\r\n晚餐{OutputDiffString(dinner)}万，宵夜{OutputDiffString(nightsnack)}万，通宵{OutputDiffString(midnight)}万";
                titleBox.TextFrame.TextRange.Font.Size = 18;
                //写入平台差异图表                
                Chart chart = currentslide.Shapes.AddChart2(
                Style: 227,
                    Type: PpChartType.xlColumnClustered,
                    Left: 0,
                    Top: 125,
                    Width: currentslide.Master.Width / 2 - 2,
                    Height: currentslide.Master.Height - 125,
                    NewLayout: true).Chart;
                // 设置图表的数据
                // 获取图表的数据源工作表  
                var worksheet = chart.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet.UsedRange.Clear();
                // 修改工作表中的数据
                worksheet.Cells[1, 1].Value = null;
                worksheet.Cells[2, 1].Value = comYear.ToString();
                worksheet.Cells[3, 1].Value = "FC";
                worksheet.Cells[4, 1].Value = "MDS";
                worksheet.Cells[5, 1].Value = "内甜";
                worksheet.Cells[6, 1].Value = "外甜";
                worksheet.Cells[7, 1].Value = year.ToString();
                worksheet.Cells[2, 2].Value = comtotal;
                worksheet.Cells[3, 2].Value = fc;
                worksheet.Cells[4, 2].Value = mds;
                worksheet.Cells[5, 2].Value = kiosk;
                worksheet.Cells[6, 2].Value = remotekiosk;
                worksheet.Cells[7, 2].Value = ctotal;
                // 设置图表的数据范围            
                string rangeAddress = "='Sheet1'!" + worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[7, 2]].Address;
                chart.SetSourceData(rangeAddress);
                // 保存并关闭Excel工作簿
                chart.ChartData.Workbook.Close(true);
                // 添加图表标题
                chart.HasTitle = true;
                chart.ChartTitle.Text = "Sales平台差异";
                // 添加图例
                chart.HasLegend = true;
                // 添加数据标签
                chart.ApplyDataLabels();
                chart.SeriesCollection(1).DataLabels().NumberFormat = "#,##0";
                //设置Q1的填充颜色为红色
                chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(250, 206, 135).ToArgb();
                chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(34, 34, 178).ToArgb();
                chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(113, 179, 60).ToArgb();
                chart.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(204, 50, 153).ToArgb();
                chart.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 154, 238).ToArgb();
                chart.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(71, 130, 255).ToArgb();
                // 取消横向网格线
                chart.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(233, 233, 238).ToArgb();
                // 取消显示横向坐标轴
                chart.Axes(XlAxisType.xlValue).MajorGridlines.Delete();
                chart.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                //chart.Axes(XlAxisType.xlValue).TickLabels.NumberFormat = ";;;";
                chart.Axes(XlAxisType.xlValue).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                // 取消显示纵向坐标轴
                chart.Axes(XlAxisType.xlCategory).MajorGridlines.Delete();
                chart.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                //chart.Axes(XlAxisType.xlCategory).TickLabels.NumberFormat = ";;;";
                chart.Axes(XlAxisType.xlCategory).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                //写入时段差异图表

                Shape shapechart2 = currentslide.Shapes.AddChart2(
                    Style: -1,
                    Type: PpChartType.xlColumnClustered,
                    Left: currentslide.Master.Width / 2,
                    Top: 125,
                    Width: currentslide.Master.Width / 2 - 2,
                    Height: currentslide.Master.Height - 125,
                    NewLayout: true);
                Chart chart2 = shapechart2.Chart;                
                // 添加数据到图表                
                var worksheet2 = chart2.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet2.UsedRange.Clear();
                // 这里只是一个示例，你需要根据实际情况添加数据               
                // 修改工作表中的数据
                worksheet2.Cells[1, 1].Value = null;
                worksheet2.Cells[2, 1].Value = "'" + comYear.ToString();
                worksheet2.Cells[3, 1].Value = "早餐";
                worksheet2.Cells[4, 1].Value = "午餐";
                worksheet2.Cells[5, 1].Value = "下午茶";
                worksheet2.Cells[6, 1].Value = "晚餐";
                worksheet2.Cells[7, 1].Value = "宵夜";
                worksheet2.Cells[8, 1].Value = "通宵";
                worksheet2.Cells[9, 1].Value = "'" + year.ToString();
                worksheet2.Cells[1, 2].Value = "时段明细";
                worksheet2.Cells[1, 3].Value = "整体";
                worksheet2.Cells[2, 3].Value = comdaytotal;
                worksheet2.Cells[3, 2].Value = breakfast;
                worksheet2.Cells[4, 2].Value = lunch;
                worksheet2.Cells[5, 2].Value = teatime;
                worksheet2.Cells[6, 2].Value = dinner;
                worksheet2.Cells[7, 2].Value = nightsnack;
                worksheet2.Cells[8, 2].Value = midnight;
                worksheet2.Cells[9, 3].Value = cdaytotal;
                // 设置图表的数据范围            
                rangeAddress = "='Sheet1'!" + worksheet2.Range[worksheet2.Cells[1, 1], worksheet2.Cells[9, 3]].Address;
                chart2.SetSourceData(rangeAddress);
                // 保存并关闭Excel工作簿
                chart2.ChartData.Workbook.Close(true);                
                //添加图表标题
                chart2.HasTitle = true;
                chart2.ChartTitle.Text = "Sales时段差异";               
                // 添加图例
                chart2.HasLegend = true;
                // 添加数据标签
                chart2.ApplyDataLabels();
                chart2.SeriesCollection(1).DataLabels().NumberFormat = "#,##0";
                chart2.SeriesCollection(2).DataLabels().NumberFormat = "#,##0";
                chart2.SeriesCollection(2).AxisGroup = XlAxisGroup.xlSecondary;                
                // 取消横向网格线
                chart2.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart2.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart2.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart2.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart2.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(233, 233, 238).ToArgb();
                //隐藏主次纵坐标轴
                chart2.Axes(XlAxisType.xlValue,XlAxisGroup.xlPrimary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                chart2.Axes(XlAxisType.xlValue,XlAxisGroup.xlSecondary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                //设置横坐标的标签位置
                chart2.Axes(XlAxisType.xlCategory).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionLow;
            }
        }

        private static string OutputDiffString(decimal diff)
        {
            return diff > 0 ? "+" + (diff / 10000).ToString("n") : (diff / 10000).ToString("n");
        }
    }
}
